﻿using System;
using System.Collections.Generic;
using System.Text;
using SmoLiteApi;
using System.Data;

namespace SmoLiteApiSample
{
    /// <summary/>
    public class Program
    {
        /// <summary/>
        public static void Main(string[] args)
        {
            Server server = new Server();

            server.ConnectionContext.ServerInstance = "localhost";
            server.ConnectionContext.Connect();

            if (server.Databases["SampleDatabase"] != null)
            {
                server.KillAllProcesses("SampleDatabase");
                server.KillDatabase("SampleDatabase");
            }

            Database database = new Database(server, "SampleDatabase");

            database.RecoveryModel = RecoveryModel.Simple;

            database.Create();

            DatabaseExtendedProperty databaseExtendedProperty = new DatabaseExtendedProperty(database, "DatabaseProperty");

            databaseExtendedProperty.Value = "abc";

            databaseExtendedProperty.Create();

            FullTextCatalog fullTextCatalog = new FullTextCatalog(database, "SampleCatalog");

            fullTextCatalog.Create();

            Schema schema = new Schema(database, "SampleSchema");

            schema.User = "dbo";

            schema.Create();

            SchemaExtendedProperty schemaExtendedProperty = new SchemaExtendedProperty(schema, "SchemaProperty");

            schemaExtendedProperty.Value = "abc";

            schemaExtendedProperty.Create();

            Table table = new Table(database, "SampleTable", schema.Name);

            Column idColumn = new Column(table, "Id");

            idColumn.DataType.SqlDbType = SqlDbType.UniqueIdentifier;

            Column nameColumn = new Column(table, "Name");

            nameColumn.DataType.SqlDbType = SqlDbType.NVarChar;
            nameColumn.DataType.MaximumLength = 100;

            Column descriptionColumn = new Column(table, "Description");

            descriptionColumn.DataType.SqlDbType = SqlDbType.NVarChar;
            descriptionColumn.DataType.MaximumLength = 1000;

            Column counterColumn = new Column(table, "Counter");

            counterColumn.DataType.SqlDbType = SqlDbType.Int;
            counterColumn.Identity = true;

            table.Columns.Add(idColumn);
            table.Columns.Add(nameColumn);
            table.Columns.Add(descriptionColumn);
            table.Columns.Add(counterColumn);

            Index tablePrimaryKey = new Index("SampleTable_SamplePrimaryKey");

            tablePrimaryKey.IsPrimaryKey = true;
            tablePrimaryKey.FillFactor = 50;

            IndexedColumn tablePrimaryKeyIndexedColumn = new IndexedColumn("Id");

            tablePrimaryKey.IndexedColumns.Add(tablePrimaryKeyIndexedColumn);

            table.Indexes.Add(tablePrimaryKey);

            Index tableIndex = new Index("SampleTable_SampleIndex");

            tableIndex.IsUnique = true;
            tableIndex.FillFactor = 50;

            IndexedColumn firstTableIndexIndexedColumn = new IndexedColumn("Name") { Ordinal = 2 };

            tableIndex.IndexedColumns.Add(firstTableIndexIndexedColumn);

            IndexedColumn secondTableIndexIndexedColumn = new IndexedColumn("Description") { Ordinal = 1 };

            tableIndex.IndexedColumns.Add(secondTableIndexIndexedColumn);

            table.Indexes.Add(tableIndex);

            table.Create();

            View view = new View(
                database,
                "SampleView",
                schema.Name,
                "CREATE VIEW SampleSchema.SampleView WITH SCHEMABINDING AS SELECT Id, Name FROM SampleSchema.SampleTable");

            Index viewIndex = new Index("SampleView_SampleIndex");

            viewIndex.IsUnique = true;
            viewIndex.IsClustered = true;
            viewIndex.FillFactor = 50;

            IndexedColumn viewIndexedColumn = new IndexedColumn("Name");

            viewIndex.IndexedColumns.Add(viewIndexedColumn);

            view.Indexes.Add(viewIndex);

            FullTextIndex fullTextIndex = new FullTextIndex();

            fullTextIndex.CatalogName = "SampleCatalog";
            fullTextIndex.UniqueIndexName = viewIndex.Name;

            fullTextIndex.IndexedColumns.Add(new FullTextIndexColumn("Name"));

            view.FullTextIndex = fullTextIndex;

            view.Create();

            StoredProcedure storedProcedure = new StoredProcedure(
                database,
                "SampleStoredProcedure",
                schema.Name,
                "CREATE PROCEDURE SampleSchema.SampleStoredProcedure AS SELECT Id, Name FROM SampleSchema.SampleTable");

            storedProcedure.Create();

            database.ExecuteNonQuery("INSERT SampleSchema.SampleTable (Id, Name, Description) VALUES(NEWID(), 'Test 1', 'Test 1 Test 1 Test 1 Test 1 Test 1 Test 1 Test 1 Test 1')\nGO\nINSERT SampleSchema.SampleTable (Id, Name, Description) VALUES(NEWID(), 'Test 2', '')\nGO\nINSERT SampleSchema.SampleTable (Id, Name, Description) VALUES(NEWID(), 'Test 3', 'Test 3')");

            foreach (View currentView in database.Views)
            {
                Console.WriteLine(currentView.Name);

                foreach (Index currentIndex in currentView.Indexes)
                {
                    Console.WriteLine("  " + currentIndex.Name);

                    foreach (IndexedColumn currentIndexedColumn in currentIndex.IndexedColumns)
                    {
                        Console.WriteLine("    " + currentIndexedColumn.Name);
                    }
                }

                if (currentView.FullTextIndex != null)
                {
                    Console.WriteLine("  " + currentView.FullTextIndex);

                    foreach (FullTextIndexColumn currentFullTextIndexColumn in currentView.FullTextIndex.IndexedColumns)
                    {
                        Console.WriteLine("    " + currentFullTextIndexColumn.Name);
                    }
                }
            }
        }
    }
}
